package cn.ctyun.ipush.utils;

import cn.ctyun.ipush.constant.ResultInfo;
import cn.ctyun.ipush.dto.ConnectionObject;
import cn.ctyun.ipush.dto.ResultDto;
import cn.ctyun.ipush.model.DataSourceModel;
import cn.ctyun.ipush.model.TaskDetailModel;
import cn.ctyun.ipush.vo.TaskVo;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.JDBC4Connection;
import com.mysql.jdbc.PreparedStatement;
import oracle.jdbc.OraclePreparedStatement;
import org.apache.commons.collections.CollectionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.print.attribute.standard.JobName;
import java.sql.*;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: user
 * Date: 16-6-7
 * Time: 下午4:40
 * To change this template use File | Settings | File Templates.
 */
public class DBUtils {

    /* 日志对象 */
    private static final Logger LOG = LoggerFactory.getLogger(DBUtils.class);


//    /**
//     * mysql 获取连接
//     *
//     * @param connectionDesc 连接串 eg: "jdbc:mysql://127.0.0.1:3306/test?user=root&password=123"
//     * @return
//     */
//    public static Connection getMysqlConnection(String connectionDesc) {
//        Connection conn = null;
//        PreparedStatement pstmt = null;
//        try {
//            long start = System.currentTimeMillis(); // 记录起始时间
//            LOG.info("mysql connection create start  \t -------------");
//            //加载驱动
//            Class.forName("com.mysql.jdbc.Driver");
//            //得到连接
//            conn = (Connection) DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?user=root&password=123");
//            if (!connectionCacheMap.containsKey(connectionDesc)) {
//                connectionCacheMap.put(connectionDesc, conn);
//            }
//            long end = System.currentTimeMillis(); // 记录起始时间
//            LOG.info("mysql connection create end : 耗时：" + String.valueOf(end - start) + "毫秒");
//            return conn;
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return null;
//    }
//
//    /**
//     * 得到mysql数据源
//     *
//     * @param connectionDesc 连接串 eg: "jdbc:mysql://127.0.0.1:3306/test?user=root&password=123"
//     * @param sql            eg:"select *  from student"
//     */
//    public static void getMysqlDataSource(String connectionDesc, String sql) {
//        Connection conn = null;
//        PreparedStatement pstmt = null;
//        try {
//            long start = System.currentTimeMillis(); // 记录起始时间
//            LOG.info("mysql execute start ----------");
//            if (DBUtils.connectionCacheMap.containsKey(connectionDesc)) {
//                conn = (com.mysql.jdbc.Connection) DBUtils.connectionCacheMap.get(connectionDesc);
//            } else {
//                conn = (com.mysql.jdbc.Connection) DBUtils.getMysqlConnection(connectionDesc);
//            }
//
//            if (DBUtils.preparedStatementCacheMap.containsKey(connectionDesc)) {
//                conn = (com.mysql.jdbc.Connection) DBUtils.preparedStatementCacheMap.get(connectionDesc);
//            } else {
//                conn = (com.mysql.jdbc.Connection) DBUtils.getMysqlConnection(connectionDesc);
//            }
//            pstmt = (com.mysql.jdbc.PreparedStatement) conn.prepareStatement(sql);
//            ResultSet rs = pstmt.executeQuery();
////            int col = rs.getMetaData().getColumnCount();
////            while (rs.next()) {
////                for (int i = 1; i <= col; i++) {
////                    System.out.print(rs.getString(i) + "\t");
////                    if ((i == 2) && (rs.getString(i).length() < 8)) {
////                        System.out.print("\t");
////                    }
////                }
////                System.out.println("");
////            }
//            pstmt.close();
//            long end = System.currentTimeMillis(); // 记录起始时间
//            LOG.info("mysql execute end : 耗时：" + String.valueOf(end - start) + "毫秒");
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//    }


    /**
     * 得到连接字符串的唯一标识符
     * url_username_password@groupName_jobname_trigger
     *
     * @param url
     * @param username
     * @param password
     * @param jobGroup
     * @param jobName
     * @param cronTriggerTime
     * @return
     */
    public static String getConnnectionIdentity(String url, String username, String password, String jobGroup, String jobName, String cronTriggerTime) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(url).append("_").append(username).append("_").append(password).append("&").append(ScheduleUtils.trigerNameFormat(jobGroup + "_" + jobName, cronTriggerTime));
        return stringBuffer.toString();
    }

    /**
     * 获取临时Oracle连接
     *
     * @param url
     * @param username
     * @param password
     * @param jobGroup
     * @param jobName
     * @param cronTrigger
     * @return
     */
    public static java.sql.Connection getTmpConnection(String url, String username, String password, String jobGroup, String jobName, String cronTrigger) {
        if (url.contains("jdbc:mysql")) {
            return getTmpMysqlConnection(url, username, password, jobGroup, jobName, cronTrigger);
        } else {
            return getTmpOracleConnection(url, username, password, jobGroup, jobName, cronTrigger);
        }
    }

    public static java.sql.Connection getTmpOracleConnection(String url, String username, String password, String jobGroup, String jobName, String cronTrigger) {
        LOG.info(String.format("--- --- ---  Create tmp oracle connection start:url:%s,userName:%s,password:%s JobGroup: %s ,JobName: %s , cronTrigger:%s---", url, username, password, jobGroup, jobName, cronTrigger));
        java.sql.Connection conn = null;
        try {
            long start = System.currentTimeMillis(); // 记录起始时间
            //加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //得到连接
            conn = (java.sql.Connection) DriverManager.getConnection(url, username, password);// DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?user=root&password=123");
            //String connectionDesc = getConnnectionIdentity(url, username, password, jobGroup, jobName, cronTrigger);
            long end = System.currentTimeMillis(); // 记录终止时间
            LOG.info("--- --- ---  Create tmp oracle  connection  end , cost time：" + String.valueOf(end - start) + "msec ---");
            return conn;
        } catch (SQLException e) {
            LOG.info(e.getMessage());
        } catch (ClassNotFoundException e) {
            LOG.info(e.getMessage());
            LOG.info("--- ---- --- Create tmp oracle  connection fail,username or userpassword or url   may have error ---");
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e1) {
                }
            }
        }
        return null;
    }

    /**
     * 获取临时Mysql连接
     *
     * @param url
     * @param username
     * @param password
     * @param jobGroup
     * @param jobName
     * @param cronTrigger
     * @return
     */
    public static java.sql.Connection getTmpMysqlConnection(String url, String username, String password, String jobGroup, String jobName, String cronTrigger) {
        LOG.info(String.format("--- --- ---  Create tmp mysql connection start:url:%s,userName:%s,password:%s JobGroup: %s ,JobName: %s , cronTrigger:%s---", url, username, password, jobGroup, jobName, cronTrigger));
        java.sql.Connection conn = null;
        try {
            long start = System.currentTimeMillis(); // 记录起始时间
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //得到连接
            String mysqlConn = String.format("%s?user=%s&password=%s", url, username, password);
            conn = (java.sql.Connection) DriverManager.getConnection(mysqlConn);// DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?user=root&password=123");
            //String connectionDesc = getConnnectionIdentity(url, username, password, jobGroup, jobName, cronTrigger);
            long end = System.currentTimeMillis(); // 记录终止时间
            LOG.info("--- --- ---  Create tmp mysql  connection  end , cost time：" + String.valueOf(end - start) + "msec ---");
            return conn;
        } catch (SQLException e) {
            LOG.info(e.getMessage());
        } catch (ClassNotFoundException e) {
            LOG.info(e.getMessage());
            LOG.info("--- ---- --- Create tmp mysql  connection fail,username or userpassword or url   may have error ---");
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e1) {
                }
            }
        }
        return null;
    }

    /**
     * 执行SQL,根据sql语句和表名
     *
     * @param conn
     * @param sql
     * @return
     */
    public static Map<String, Object> executeOracleSelectBySql(java.sql.Connection conn, String sql, String tableName) {
        Map<String, Object> rsMap = new HashMap<String, Object>();
        OraclePreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            long start = System.currentTimeMillis(); // 记录起始时间
            LOG.info(String.format(" Oracle execute sql: %s  start ----------", sql));
            pstmt = (OraclePreparedStatement) conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rsMap.put("ps", pstmt);
            rs = pstmt.executeQuery();
            rsMap.put("rs", rs);
            rsMap.put("tableName", tableName);
            rsMap.put("sql", sql);
            //int col = rs.getMetaData().getColumnCount();
            long end = System.currentTimeMillis(); // 记录起始时间
            LOG.info(String.format("--- Oracle execute sql: %s end : cost time：%d msec", sql, end - start));
            return rsMap;
        } catch (SQLException e) {
            if (null != pstmt)
                try {
                    pstmt.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
                }
            return null;
        }
    }

    /**
     * 执行SQL,根据表名
     *
     * @param conn
     * @param tableName
     * @return {"ps":psmt,"rs":rs,"tableName":tableName,"sql":sql}
     */
    public static Map<String, Object> executeSelectByTable(java.sql.Connection conn, String tableName) {
        try {
            if (conn != null) {
                String myUrl = conn.getMetaData().getURL();
                if (myUrl.contains("jdbc:mysql")) {
                    return executeMysqlSelectByTable(conn, tableName);
                } else {
                    return executeOracleSelectByTable(conn, tableName);
                }
            }
        } catch (SQLException e) {
            LOG.error(e.getMessage());
            return null;
        }
        return null;
    }

    /**
     * 执行SQL,根据表名
     *
     * @param conn
     * @param tableName
     * @return {"ps":psmt,"rs":rs,"tableName":tableName,"sql":sql}
     */
    public static Map<String, Object> executeOracleSelectByTable(java.sql.Connection conn, String tableName) {
        Map<String, Object> rsMap = new HashMap<String, Object>();
        OraclePreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            long start = System.currentTimeMillis(); // 记录起始时间
            LOG.info(String.format(" Oracle execute table: %s  start ----------", tableName));
            String sql = String.format("select *  from %s ", tableName);
            pstmt = (OraclePreparedStatement) conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rsMap.put("ps", pstmt);
            rs = pstmt.executeQuery();
            rsMap.put("rs", rs);
            rsMap.put("tableName", tableName);
            rsMap.put("sql", sql);
            //int col = rs.getMetaData().getColumnCount();
            long end = System.currentTimeMillis();
            LOG.info(String.format("--- Oracle execute sql: %s end : cost time：%d msec", sql, end - start));
            return rsMap;
        } catch (SQLException e) {
            if (null != pstmt)
                try {
                    pstmt.close();
                } catch (SQLException e1) {
                    LOG.error("执行器关闭失败，message:" + e1.getMessage());
                }
            return null;
        }
    }

    /**
     * 执行,mysql SQL,根据表名
     *
     * @param conn
     * @param tableName
     * @return {"ps":psmt,"rs":rs,"tableName":tableName,"sql":sql}
     */
    public static Map<String, Object> executeMysqlSelectByTable(java.sql.Connection conn, String tableName) {
        Map<String, Object> rsMap = new HashMap<String, Object>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            long start = System.currentTimeMillis(); // 记录起始时间
            LOG.info(String.format(" Mysql execute table: %s  start ----------", tableName));
            String sql = String.format("select *  from %s ", tableName);
            pstmt = (PreparedStatement) conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rsMap.put("ps", pstmt);
            rs = pstmt.executeQuery();
            rsMap.put("rs", rs);
            rsMap.put("tableName", tableName);
            rsMap.put("sql", sql);
            //int col = rs.getMetaData().getColumnCount();
            long end = System.currentTimeMillis();
            LOG.info(String.format("--- Mysql execute sql: %s end : cost time：%d msec", sql, end - start));
            return rsMap;
        } catch (SQLException e) {
            if (null != pstmt)
                try {
                    pstmt.close();
                } catch (SQLException e1) {
                    LOG.error("执行器关闭失败，message:" + e1.getMessage());
                }
            return null;
        }
    }

    /**
     * 检测连接是否成功
     */
    public static ResultDto isConectionSuccess(String url, String username, String password, String tableName) {
        if (url.contains("jdbc:mysql")) {
            return isMysqlConectionSuccess(url, username, password, tableName);
        } else {
            return isOracleConectionSuccess(url, username, password, tableName);
        }
    }

    /**
     * 检测oracle连接是否成功
     */
    public static ResultDto isOracleConectionSuccess(String url, String username, String password,String tableName, String sql) {
        java.sql.Connection conn = null;
        OraclePreparedStatement pstmt = null;
        ResultSet rs = null;
        ResultDto resultDto = new ResultDto();
        try {
            //加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //得到连接
            conn = (java.sql.Connection) DriverManager.getConnection(url, username, password);// DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?user=root&password=123");
            if (null != sql) {
                pstmt = (OraclePreparedStatement) conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                rs = pstmt.executeQuery();
                rs.close();
                pstmt.close();
            }
            return resultDto.setStatus(ResultInfo.success);
        } catch (ClassNotFoundException e) {
            LOG.info(e.getMessage());
            return resultDto.setStatus(ResultInfo.failure).setInfo(e.getMessage());
        } catch (SQLException e) {
            LOG.info(e.getMessage());
            return resultDto.setStatus(ResultInfo.failure).setInfo(e.getMessage());
        } catch (Exception ex) {
            LOG.info(ex.getMessage());
            return resultDto.setStatus(ResultInfo.failure).setInfo(ex.getMessage());
        } finally {
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                LOG.error("连接出错：" + e.getMessage());
            }
        }
    }

    /**
     * 检测oracle连接是否成功
     */
    public static ResultDto isOracleConectionSuccess(String url, String username, String password, String tableName) {
        java.sql.Connection conn = null;
        OraclePreparedStatement pstmt = null;
        ResultSet rs = null;
        ResultDto resultDto = new ResultDto();
        try {
            //加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //得到连接
            conn = (java.sql.Connection) DriverManager.getConnection(url, username, password);// DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?user=root&password=123");
            if (null != tableName) {
                String sql = "select *  from " + tableName;
                pstmt = (OraclePreparedStatement) conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                rs = pstmt.executeQuery();
                rs.close();
                pstmt.close();
            }
            return resultDto.setStatus(ResultInfo.success);
        } catch (ClassNotFoundException e) {
            LOG.info(e.getMessage());
            return resultDto.setStatus(ResultInfo.failure).setInfo(e.getMessage());
        } catch (SQLException e) {
            LOG.info(e.getMessage());
            return resultDto.setStatus(ResultInfo.failure).setInfo(e.getMessage());
        } catch (Exception ex) {
            LOG.info(ex.getMessage());
            return resultDto.setStatus(ResultInfo.failure).setInfo(ex.getMessage());
        } finally {
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                LOG.error("连接出错：" + e.getMessage());
            }
        }
    }

    /**
     * 检测mysql连接是否成功
     */
    public static ResultDto isMysqlConectionSuccess(String url, String username, String password, String tableName) {
        java.sql.Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ResultDto resultDto = new ResultDto();
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //得到连接
            String mysqlConn = String.format("%s?user=%s&password=%s", url, username, password);
            conn = (java.sql.Connection) DriverManager.getConnection(mysqlConn);// DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?user=root&password=123");
            if (null != tableName) {
                String sql = "select *  from " + tableName;
                pstmt = (PreparedStatement) conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                rs = pstmt.executeQuery();
                rs.close();
                pstmt.close();
            }
            return resultDto.setStatus(ResultInfo.success);
        } catch (ClassNotFoundException e) {
            LOG.info(e.getMessage());
            return resultDto.setStatus(ResultInfo.failure).setInfo(e.getMessage());
        } catch (SQLException e) {
            LOG.info(e.getMessage());
            return resultDto.setStatus(ResultInfo.failure).setInfo(e.getMessage());
        } catch (Exception ex) {
            LOG.info(ex.getMessage());
            return resultDto.setStatus(ResultInfo.failure).setInfo(ex.getMessage());
        } finally {
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                LOG.error("连接出错：" + e.getMessage());
            }
        }
    }
}
